/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package wf.units;

import bc.swing.pfrm.units.EventBusUnit;
import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.dao.DaoManager;
import com.j256.ormlite.dao.GenericRawResults;
import com.j256.ormlite.dao.RawRowMapper;
import com.j256.ormlite.db.MysqlDatabaseType;
import com.j256.ormlite.jdbc.JdbcConnectionSource;
import com.j256.ormlite.support.ConnectionSource;
import com.j256.ormlite.table.TableUtils;
import java.nio.channels.NotYetConnectedException;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import wf.model.Counter;
import wf.model.PLC;
import wf.model.Schedule;
import wf.model.Statistic;
import wf.model.User;
import wf.model.Valve;
import wf.ui.StatisticsManager.StatisticsResolution;

/**
 *
 * @author bennyl
 */
public enum DatabaseUnit {

    UNIT;
    public static final String DBCE_DATA = "IOME DATA";
    public static final String DBCE_DATA_CLASS = "IOME DATA CLASS";
    public static final String DBCE_CHANGE_TYPE = "IOME CHANGE TYPE";
    public static final String DATABASE_CHANGED_EVENT = "INSERT OR MODIFIED EVENT";
    private static final String DATABASE_IP = "localhost";
    private static final String DATABASE_PASS = "project-hit";
    private static final String DBURL = "jdbc:mysql://" + DATABASE_IP + ":3306/waterfall?user=root&password=" + DATABASE_PASS;
    private ConnectionSource cs;
    private boolean connected = false;

    public void connect() {
        try {
            cs = new JdbcConnectionSource(DBURL, new MysqlDatabaseType());
            connected = true;
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseUnit.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void validateConnection() {
        if (!connected) {
            throw new NotYetConnectedException();
        }
    }

    private <D, T> Dao<T, D> daoFor(Class<T> clazz) {
        try {
            return DaoManager.createDao(cs, clazz);
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseUnit.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    private Dao dao() {
        return daoFor(User.class);
    }

    public static String date2sql(Date d) {
        return "'" + (d.getYear() + 1900) + "-" + (d.getMonth() + 1) + "-" + d.getDate() + "'";
    }

    public static Date sql2date(String date, boolean withTime) {
        System.out.println("Received Date: " + date);
        final String[] split = date.split(" ");

        String[] parts = split[0].split("-");
        final int year = cint(parts[0]) - 1900;
        final int month = cint(parts[1]) - 1;
        final int day = cint(parts[2]);
        if (!withTime) {
            return new Date(year, month, day);
        } else {
            String[] iparts = split[1].split(":");

            int hours = cint(iparts[0]);
            int min = cint(iparts[1]);
            return new Date(year, month, day, hours, min);
        }
    }

    public static int cint(String s) {
        return Integer.parseInt(s);
    }

    private String populateStatment(String statment, String... population) {
        for (int i = 0; i < population.length; i += 2) {
            statment = statment.replace("${" + population[i] + "}", population[i + 1]);
        }

        return statment;
    }

    public List<Statistic> collectGeneralWaterConsumptionStatistics(Date startDate, Date endDate, StatisticsResolution resolution) {
        String statment = ""
                + "SELECT SUM(cdata.read)*c.pulseResolution AS cnt, c.name "
                + "FROM waterfall.counters_data AS cdata , waterfall.counters AS c "
                + "WHERE cdata.date BETWEEN  ${START DATE}  AND  ${END DATE} AND c.id = cdata.counterNumber "
                + "GROUP BY c.name";

        statment = populateStatment(statment, "START DATE", date2sql(startDate), "END DATE", date2sql(endDate));
        System.out.println("Query: " + statment);

        GenericRawResults<Map<String, String>> data = query(statment);
        LinkedList<Statistic> ret = new LinkedList<Statistic>();
        for (Map<String, String> d : data) {
            ret.add(new Statistic(d.get("name"), Integer.parseInt(d.get("cnt"))));
        }

        return ret;
    }

    public List<Statistic> collectWaterStatisticsBySingleCounter(Date start, Date end, StatisticsResolution tres, int counterIds) {
        String statment = ""
                + "SELECT SUM(cdata.read)*c.pulseResolution AS cnt,cdata.date "
                + "FROM waterfall.counters_data AS cdata , waterfall.counters AS c "
                + "WHERE cdata.date BETWEEN ${START DATE} AND ${END DATE} AND c.id = cdata.counterNumber "
                + "AND cdata.counterNumber = ${COUNTER ID} "
                + "GROUP BY DATE_FORMAT(cdata.date, ${DATE FILTER}), counterNumber";

        statment = populateStatment(statment, "START DATE", date2sql(start), "END DATE", date2sql(end), "COUNTER ID", "" + counterIds, "DATE FILTER", tres.getSqlFilter());

        GenericRawResults<Map<String, String>> data = query(statment);
        LinkedList<Statistic> ret = new LinkedList<Statistic>();
        for (Map<String, String> d : data) {
            ret.add(new Statistic(cint(d.get("cnt")), sql2date(d.get("date"), true)));
        }

        return ret;
    }

    public List<Schedule> selectSchedulesForMonth(int month, int year) {
        String startDate = "'" + year + "-" + month + "-01'";

        try {
            return daoFor(Schedule.class).queryBuilder().where().raw("dayTime BETWEEN " + startDate + " AND (" + startDate + " + INTERVAL 1 MONTH - INTERVAL 1 DAY)").query();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseUnit.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public <T> T selectItem(Class<T> type, Object id) {
        try {
            return daoFor(type).queryForId(id);
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseUnit.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public List<Schedule> selectTenClosestSchedules(int toValve) {
        String statment = ""
                + "SELECT * FROM waterfall.scheduling "
                + "WHERE dayTime >= current_date AND valveId = " + toValve + " "
                + "ORDER BY dayTime LIMIT 0, 10";

        GenericRawResults<Map<String, String>> data = query(statment);
        LinkedList<Schedule> ret = new LinkedList<Schedule>();
        for (Map<String, String> d : data) {
            ret.add(new Schedule(Integer.parseInt(d.get("valveId")), sql2date(d.get("dayTime"), true), Integer.parseInt(d.get("periodMinute"))));
        }

        return ret;
    }

    public void deleteItem(Object item) {
        try {
            Dao dao = daoFor(item.getClass());
            dao.delete(item);
            EventBusUnit.UNIT.fire(DATABASE_CHANGED_EVENT, DBCE_DATA_CLASS, item.getClass(), DBCE_DATA, item, DBCE_CHANGE_TYPE, ChangeType.DELETE);
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseUnit.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public List<Schedule> selectSchedulesForDay(int day, int month, int year) {

        try {
            return daoFor(Schedule.class).queryBuilder().where().raw("DATE(dayTime) = '" + year + "-" + month + "-" + day + "'").query();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseUnit.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public void recreateTable(Class tableClass) {
        try {
            TableUtils.createTable(cs, tableClass);
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseUnit.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public boolean insertOrUpdate(Object o, boolean notifyChanges) {
        try {
            Dao dao = daoFor(o.getClass());
            dao.createOrUpdate(o);
            if (notifyChanges) {
                EventBusUnit.UNIT.fire(DATABASE_CHANGED_EVENT, DBCE_DATA_CLASS, o.getClass(), DBCE_DATA, o, DBCE_CHANGE_TYPE, ChangeType.EDIT_OR_INSERT);
            }
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseUnit.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }

    }

    public boolean insertOrUpdate(Object o) {
        return insertOrUpdate(o, true);
    }

    public PLC selectPLCByIP(String ip) {
        validateConnection();

        try {
            return daoFor(PLC.class).queryForId(ip);
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseUnit.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public User selectUserByName(String name) {
        validateConnection();

        try {
            return daoFor(User.class).queryForId(name);
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseUnit.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public List<Valve> selectAllValves() {
        validateConnection();
        try {
            return daoFor(Valve.class).queryForAll();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseUnit.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public List<Counter> selectAllCounters() {
        validateConnection();
        try {
            return daoFor(Counter.class).queryForAll();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseUnit.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public List<PLC> selectAllPLCs() {
        validateConnection();

        try {
            return daoFor(PLC.class).queryForAll();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseUnit.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public List<Valve> selectValvesOnPLC(String plcIP) {
        try {
            return daoFor(Valve.class).queryBuilder().where().eq("plcIp", plcIP).query();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseUnit.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public List<Counter> selectCountersOnPLC(String plcIP) {
        try {
            return daoFor(Counter.class).queryBuilder().where().eq("plcIP", plcIP).query();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseUnit.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public List<User> selectAllUsers() {
        validateConnection();

        try {
            return daoFor(User.class).queryForAll();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseUnit.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public GenericRawResults<Map<String, String>> query(String query) {
        try {
            return dao().queryRaw(query, new MapMapper());
        } catch (SQLException ex) {
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public static void main(String[] args) {
        UNIT.connect();
//        UNIT.recreateTable(Schedule.class);
        for (Schedule s : UNIT.selectTenClosestSchedules(1)) {
            System.out.println("" + s.toString());
        }
    }

    public static class MapMapper implements RawRowMapper<Map<String, String>> {

        @Override
        public Map<String, String> mapRow(String[] columnNames, String[] resultColumns) throws SQLException {
            HashMap<String, String> ret = new HashMap<String, String>();
            for (int i = 0; i < columnNames.length; i++) {
                ret.put(columnNames[i], resultColumns[i]);
            }

            return ret;
        }
    }
    
    public static enum ChangeType{
        EDIT_OR_INSERT,
        DELETE,
    }
}
